"""
Case Type   : Ubtree
Case Name   : 建表插入数据，创建ubtree索引指定pcr_使用gs_backup备份、恢复
Create At   : 2025-10-11
Owner       : sungang14
Description :
    1、建表;
    2、建索引时指定PCR
    3、插入数据
    4、执行备份
    5、继续插入数据，停止数据库（gs_backup不恢复表，删除不行）
    6、恢复
    7、查询计划验证索引正常
    8、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.common = Common()
        self.node_pri = Node(node='PrimaryDbUser')
        self.node_std1 = Node(node='Standby1DbUser')
        self.node_std2 = Node(node='Standby2DbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0104'
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"""create table {self.table_name} (
                                        id int primary key,
                                        dept varchar(20),
                                        name varchar(50),
                                        salary numeric(10,2)
                                    ) with (storage_type=ustore);"""
        self.prepare_data_02 = f"create index i_ubtree_0104 on {self.table_name} using ubtree(id) with (index_type=pcr);"
        self.prepare_data_03 = f"insert into {self.table_name} select generate_series(1,1000000),'dep'||(random()*5)::int, 'emp_'||lpad((random()*999999)::int::text,5,'0'),(random()*100000+50000)::numeric(10,2);"
        self.prepare_data_05 = f"insert into {self.table_name} select generate_series(1000001,1000100),'dep'||(random()*5)::int, 'emp_'||lpad((random()*999999)::int::text,5,'0'),(random()*100000+50000)::numeric(10,2);"
        self.prepare_data_04 = f"explain (costs off) select * from {self.table_name} where id < 10000;"
        self.backup_dir = f"ubtree_test_0104"
        self.backup_path = os.path.join(os.path.dirname(macro.DB_INSTANCE_PATH), self.backup_dir)

    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '-----step4:执行备份;expect:成功-----'
        logger.info(text)
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_backup -t backup --backup-dir={self.backup_path} --all"
        res = self.common.get_sh_result(self.node_pri, cmd)
        self.assertIn('Successfully backed up cluster files', res,
                      '执行失败：' + text)

        text = '--step5:继续插入数据，关闭数据库;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)
        stop_res = self.comsh.stop_db_instance()
        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)

        text = '--step6:恢复;expect:成功--'
        logger.info(text)
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_backup -t restore --backup-dir={self.backup_path} --all"
        logger.info(cmd)
        res = self.common.get_sh_result(self.node_pri, cmd)
        self.assertIn('Successfully restored cluster files', res,
                      '执行失败：' + text)
        start_res = self.comsh.start_db_instance()
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, start_res,
                      '执行失败：' + text)

        text = '--step7:验证索引可用;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.EXPLAIN_SUCCESS_MSG, data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        cmd = f'rm -rf {self.backup_path};echo $?;'
        logger.info(cmd)
        cmd_res = self.node_pri.sh(cmd).result()
        cmd_res1 = self.node_std1.sh(cmd).result()
        cmd_res2 = self.node_std2.sh(cmd).result()
        logger.info(cmd_res)
        self.assertEqual('0', cmd_res, f'执行失败:{text}')
        self.assertEqual('0', cmd_res1, f'执行失败:{text}')
        self.assertEqual('0', cmd_res2, f'执行失败:{text}')
        restart_db_cluster = self.comsh.restart_db_cluster()
        self.assertTrue(restart_db_cluster, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')